/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import com.webaccess.retailws.Bean.BrandBean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.webaccess.retailws.db.DBConnection;
import java.util.*;

/**
 *
 * @author Owner
 */
public class BrandDAO {

    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    String result = null;
    StringBuffer myQuery = null;
    BrandBean brandBeanObj = null;
    CommonDAO commDAO = null;

    public BrandDAO() {
        try {
            dbc = new DBConnection();
            //conn = (Connection) dbc.getConnection();
            commDAO = new CommonDAO();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public BrandBean[] getAllBrand() {
        BrandBean[] brandList = null;
        brandBeanObj = new BrandBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectbrand('0')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    brandList = setAllBrand(rs);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return brandList;
        }
    }

    private BrandBean[] setAllBrand(ResultSet rs) {
        List alBrand = new ArrayList();
        try {
            alBrand.clear();
            while (rs.next()) {
                brandBeanObj = new BrandBean();
                brandBeanObj.setBrandId(rs.getLong("brand_id"));
                brandBeanObj.setBrandName(rs.getString("brand"));
                brandBeanObj.setBrandTitle(rs.getString("brandtitle"));
                brandBeanObj.setBrandDesc(rs.getString("description"));
                brandBeanObj.setBrandLogoName(rs.getString("brandlogo"));
                brandBeanObj.setBrandStatus(rs.getString("status"));
                brandBeanObj.setBrandStatusId(rs.getLong("status_id"));
                alBrand.add(brandBeanObj);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (BrandBean[]) alBrand.toArray(new BrandBean[0]);
        }
    }

    public BrandBean getBrand(Long brandId) {
        brandBeanObj = new BrandBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectbrand('");
            myQuery.append(brandId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    brandBeanObj = setBrand(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return brandBeanObj;
        }
    }

    private BrandBean setBrand(ResultSet rs) {
        try {
            while (rs.next()) {
                brandBeanObj = new BrandBean();
                brandBeanObj.setBrandId(rs.getLong("brand_id"));
                brandBeanObj.setBrandName(rs.getString("brand"));
                brandBeanObj.setBrandTitle(rs.getString("brandtitle"));
                brandBeanObj.setBrandDesc(rs.getString("description"));
                brandBeanObj.setBrandLogoName(rs.getString("brandlogo"));
                brandBeanObj.setBrandStatus(rs.getString("status"));
                brandBeanObj.setBrandStatusId(rs.getLong("status_id"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return brandBeanObj;
        }
    }

    public LinkedHashMap getBrandLHMap() {
        LinkedHashMap brandLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("SELECT 	`brand_id`,`brand` FROM `retailws`.`brand`");
        brandLHMap = commDAO.getData(myQuery.toString());
        return brandLHMap;
    }

    public boolean saveBrand(BrandBean brandBeanObj) {
        boolean isBrandSaved = false;
        Long brandId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            if (brandBeanObj.getBrandId() == null) {
                myQuery.append("CALL sp_savebrand(NULL,'");
            } else {
                myQuery.append("CALL sp_savebrand('");
                myQuery.append(brandBeanObj.getBrandId());
                myQuery.append("','");
            }
            myQuery.append(brandBeanObj.getBrandName());
            myQuery.append("','");
            myQuery.append(brandBeanObj.getBrandDesc());
            myQuery.append("','");
            myQuery.append(brandBeanObj.getBrandLogoName());
            myQuery.append("','");
            myQuery.append(brandBeanObj.getBrandStatusId());
            myQuery.append("','");
            myQuery.append(brandBeanObj.getBrandTitle());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (brandBeanObj.getBrandId() == null) {
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        while (rs.next()) {
                            brandId = rs.getLong("sp_BrandId");
                        }
                    }
                }
                if (brandId > 0) {
                    isBrandSaved = true;
                }
            } else {
                isBrandSaved = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isBrandSaved;
        }
    }

    public boolean deleteBrand(Long brandId) {
        boolean isBrandDeleted = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deletebrand('");
            myQuery.append(brandId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isBrandDeleted = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isBrandDeleted;
        }
    }

    public BrandBean[] searchBrand(Long brandStatusId, String brandName) {
        BrandBean[] brandList = null;
        brandBeanObj = new BrandBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_searchBrand(");
            if (brandStatusId > 0) {
                myQuery.append("'");
                myQuery.append(brandStatusId);
                myQuery.append("',NULL)");
            } else {
                myQuery.append("NULL,'");
                myQuery.append(brandName);
                myQuery.append("%')");
            }
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    brandList = setAllBrand(rs);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return brandList;
        }
    }

    public BrandBean[] getBrandProductList() {
        BrandBean[] brandList = null;
        brandBeanObj = new BrandBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("SELECT brand_id, brand FROM brand WHERE brand_id IN (SELECT DISTINCT(brand_id) FROM `product`) LIMIT 0,7");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    brandList = setBrandProductList(rs);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return brandList;
        }
    }

    private BrandBean[] setBrandProductList(ResultSet rs) {
        List alBrand = new ArrayList();
        try {
            alBrand.clear();
            while (rs.next()) {
                brandBeanObj = new BrandBean();
                brandBeanObj.setBrandId(rs.getLong("brand_id"));
                brandBeanObj.setBrandName(rs.getString("brand"));
                alBrand.add(brandBeanObj);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (BrandBean[]) alBrand.toArray(new BrandBean[0]);
        }
    }
}
